Hệ thống xếp lịch học tín chỉ cho sinh viên CNTT trên PHP & MySQL
111.106 lượt xem;
- updateDB.php
- project /
1 <?php
2 // check this file's MD5 to make sure it wasn't called before
3 $prevMD5=@implode('', @file(dirname(__FILE__).'/setup.md5'));
4 $thisMD5=md5(@implode('', @file("./updateDB.php")));
5 if($thisMD5==$prevMD5){
6 $setupAlreadyRun=true;
7 }else{
8 // set up tables
9 if(!isset($silent)){
10 $silent=true;
11 }
12
13 // set up tables
14 setupTable('schools', "create table if not exists `schools` ( `id` INT unsigned not null auto_increment , primary key (`id`), `name` VARCHAR(40) not null ) CHARSET utf8", $silent);
15 setupTable('departments', "create table if not exists `departments` ( `id` INT unsigned not null auto_increment , primary key (`id`), `name` VARCHAR(40) not null , `school` INT unsigned not null ) CHARSET utf8", $silent);
16 setupIndexes('departments', array('school'));
17 setupTable('class_time_table', "create table if not exists `class_time_table` ( `id` INT unsigned not null auto_increment , primary key (`id`), `day` VARCHAR(40) not null , `time_start` TIME not null , `time_end` TIME not null , `unit_code` VARCHAR(40) not null , `venue` VARCHAR(40) not null , `school` INT unsigned not null , `department` INT unsigned not null , `year_of_study` VARCHAR(40) not null ) CHARSET utf8", $silent);
18 setupIndexes('class_time_table', array('school','department'));
19 setupTable('exam_time_table', "create table if not exists `exam_time_table` ( `id` INT unsigned not null auto_increment , primary key (`id`), `date` DATE not null , `time_start` TIME not null , `time_end` TIME not null , `unit_code` VARCHAR(40) not null , `venue` VARCHAR(40) not null , `school` INT unsigned not null , `department` INT unsigned not null , `year_of_study` VARCHAR(40) not null ) CHARSET utf8", $silent);
20 setupIndexes('exam_time_table', array('school','department'));
21 setupTable('personal_time_table', "create table if not exists `personal_time_table` ( `id` INT unsigned not null auto_increment , primary key (`id`), `day` VARCHAR(40) not null , `time_start` TIME not null , `time_end` TIME not null , `activity` VARCHAR(40) not null ) CHARSET utf8", $silent);
22 setupTable('student_details', "create table if not exists `student_details` ( `id` INT unsigned not null auto_increment , primary key (`id`), `full_name` VARCHAR(40) not null , `school` INT unsigned not null , `department` INT unsigned not null , `year_of_study` VARCHAR(40) not null , `reg_no` VARCHAR(40) not null , unique `reg_no_unique` (`reg_no`)) CHARSET utf8", $silent, array( "ALTER TABLE `student_details` ADD UNIQUE `reg_no_unique` (`reg_no`)"));
23 setupIndexes('student_details', array('school','department'));
24 setupTable('notices', "create table if not exists `notices` ( `id` INT unsigned not null auto_increment , primary key (`id`), `notice` TEXT not null , `school` INT unsigned not null , `department` INT unsigned not null , `year_of_study` VARCHAR(40) not null , `date` DATE ) CHARSET utf8", $silent);
25 setupIndexes('notices', array('school','department'));
26
27
28 // save MD5
29 if($fp=@fopen(dirname(__FILE__).'/setup.md5', 'w')){
30 fwrite($fp, $thisMD5);
31 fclose($fp);
32 }
33 }
34
35
36 function setupIndexes($tableName, $arrFields){
37 if(!is_array($arrFields)){
38 return false;
39 }
40
41 foreach($arrFields as $fieldName){
42 if(!$res=@db_query("SHOW COLUMNS FROM `$tableName` like '$fieldName'")){
43 continue;
44 }
45 if(!$row=@db_fetch_assoc($res)){
46 continue;
47 }
48 if($row['Key']==''){
49 @db_query("ALTER TABLE `$tableName` ADD INDEX `$fieldName` (`$fieldName`)");
50 }
51 }
52 }
53
54
55 function setupTable($tableName, $createSQL='', $silent=true, $arrAlter=''){
56 global $Translation;
57 ob_start();
58
59 echo '<div style="padding: 5px; border-bottom:solid 1px silver; font-family: verdana, arial; font-size: 10px;">';
60
61 // is there a table rename query?
62 if(is_array($arrAlter)){
63 $matches=array();
64 if(preg_match("/ALTER TABLE `(.*)` RENAME `$tableName`/", $arrAlter[0], $matches)){
65 $oldTableName=$matches[1];
66 }
67 }
68
69 if($res=@db_query("select count(1) from `$tableName`")){ // table already exists
70 if($row = @db_fetch_array($res)){
71 echo str_replace("<TableName>", $tableName, str_replace("<NumRecords>", $row[0],$Translation["table exists"]));
72 if(is_array($arrAlter)){
73 echo '<br>';
74 foreach($arrAlter as $alter){
75 if($alter!=''){
76 echo "$alter ... ";
77 if(!@db_query($alter)){
78 echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
79 echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
80 }else{
81 echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
82 }
83 }
84 }
85 }else{
86 echo $Translation["table uptodate"];
87 }
88 }else{
89 echo str_replace("<TableName>", $tableName, $Translation["couldnt count"]);
90 }
91 }else{ // given tableName doesn't exist
92
93 if($oldTableName!=''){ // if we have a table rename query
94 if($ro=@db_query("select count(1) from `$oldTableName`")){ // if old table exists, rename it.
95 $renameQuery=array_shift($arrAlter); // get and remove rename query
96
97 echo "$renameQuery ... ";
98 if(!@db_query($renameQuery)){
99 echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
100 echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
101 }else{
102 echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
103 }
104
105 if(is_array($arrAlter)) setupTable($tableName, $createSQL, false, $arrAlter); // execute Alter queries on renamed table ...
106 }else{ // if old tableName doesn't exist (nor the new one since we're here), then just create the table.
107 setupTable($tableName, $createSQL, false); // no Alter queries passed ...
108 }
109 }else{ // tableName doesn't exist and no rename, so just create the table
110 echo str_replace("<TableName>", $tableName, $Translation["creating table"]);
111 if(!@db_query($createSQL)){
112 echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
113 echo '<div class="text-danger">' . $Translation['mysql said'] . db_error(db_link()) . '</div>';
114 }else{
115 echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
116 }
117 }
118 }
119
120 echo "</div>";
121
122 $out=ob_get_contents();
123 ob_end_clean();
124 if(!$silent){
125 echo $out;
126 }
127 }
128 ?>
2 // check this file's MD5 to make sure it wasn't called before
3 $prevMD5=@implode('', @file(dirname(__FILE__).'/setup.md5'));
4 $thisMD5=md5(@implode('', @file("./updateDB.php")));
5 if($thisMD5==$prevMD5){
6 $setupAlreadyRun=true;
7 }else{
8 // set up tables
9 if(!isset($silent)){
10 $silent=true;
11 }
12
13 // set up tables
14 setupTable('schools', "create table if not exists `schools` ( `id` INT unsigned not null auto_increment , primary key (`id`), `name` VARCHAR(40) not null ) CHARSET utf8", $silent);
15 setupTable('departments', "create table if not exists `departments` ( `id` INT unsigned not null auto_increment , primary key (`id`), `name` VARCHAR(40) not null , `school` INT unsigned not null ) CHARSET utf8", $silent);
16 setupIndexes('departments', array('school'));
17 setupTable('class_time_table', "create table if not exists `class_time_table` ( `id` INT unsigned not null auto_increment , primary key (`id`), `day` VARCHAR(40) not null , `time_start` TIME not null , `time_end` TIME not null , `unit_code` VARCHAR(40) not null , `venue` VARCHAR(40) not null , `school` INT unsigned not null , `department` INT unsigned not null , `year_of_study` VARCHAR(40) not null ) CHARSET utf8", $silent);
18 setupIndexes('class_time_table', array('school','department'));
19 setupTable('exam_time_table', "create table if not exists `exam_time_table` ( `id` INT unsigned not null auto_increment , primary key (`id`), `date` DATE not null , `time_start` TIME not null , `time_end` TIME not null , `unit_code` VARCHAR(40) not null , `venue` VARCHAR(40) not null , `school` INT unsigned not null , `department` INT unsigned not null , `year_of_study` VARCHAR(40) not null ) CHARSET utf8", $silent);
20 setupIndexes('exam_time_table', array('school','department'));
21 setupTable('personal_time_table', "create table if not exists `personal_time_table` ( `id` INT unsigned not null auto_increment , primary key (`id`), `day` VARCHAR(40) not null , `time_start` TIME not null , `time_end` TIME not null , `activity` VARCHAR(40) not null ) CHARSET utf8", $silent);
22 setupTable('student_details', "create table if not exists `student_details` ( `id` INT unsigned not null auto_increment , primary key (`id`), `full_name` VARCHAR(40) not null , `school` INT unsigned not null , `department` INT unsigned not null , `year_of_study` VARCHAR(40) not null , `reg_no` VARCHAR(40) not null , unique `reg_no_unique` (`reg_no`)) CHARSET utf8", $silent, array( "ALTER TABLE `student_details` ADD UNIQUE `reg_no_unique` (`reg_no`)"));
23 setupIndexes('student_details', array('school','department'));
24 setupTable('notices', "create table if not exists `notices` ( `id` INT unsigned not null auto_increment , primary key (`id`), `notice` TEXT not null , `school` INT unsigned not null , `department` INT unsigned not null , `year_of_study` VARCHAR(40) not null , `date` DATE ) CHARSET utf8", $silent);
25 setupIndexes('notices', array('school','department'));
26
27
28 // save MD5
29 if($fp=@fopen(dirname(__FILE__).'/setup.md5', 'w')){
30 fwrite($fp, $thisMD5);
31 fclose($fp);
32 }
33 }
34
35
36 function setupIndexes($tableName, $arrFields){
37 if(!is_array($arrFields)){
38 return false;
39 }
40
41 foreach($arrFields as $fieldName){
42 if(!$res=@db_query("SHOW COLUMNS FROM `$tableName` like '$fieldName'")){
43 continue;
44 }
45 if(!$row=@db_fetch_assoc($res)){
46 continue;
47 }
48 if($row['Key']==''){
49 @db_query("ALTER TABLE `$tableName` ADD INDEX `$fieldName` (`$fieldName`)");
50 }
51 }
52 }
53
54
55 function setupTable($tableName, $createSQL='', $silent=true, $arrAlter=''){
56 global $Translation;
57 ob_start();
58
59 echo '<div style="padding: 5px; border-bottom:solid 1px silver; font-family: verdana, arial; font-size: 10px;">';
60
61 // is there a table rename query?
62 if(is_array($arrAlter)){
63 $matches=array();
64 if(preg_match("/ALTER TABLE `(.*)` RENAME `$tableName`/", $arrAlter[0], $matches)){
65 $oldTableName=$matches[1];
66 }
67 }
68
69 if($res=@db_query("select count(1) from `$tableName`")){ // table already exists
70 if($row = @db_fetch_array($res)){
71 echo str_replace("<TableName>", $tableName, str_replace("<NumRecords>", $row[0],$Translation["table exists"]));
72 if(is_array($arrAlter)){
73 echo '<br>';
74 foreach($arrAlter as $alter){
75 if($alter!=''){
76 echo "$alter ... ";
77 if(!@db_query($alter)){
78 echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
79 echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
80 }else{
81 echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
82 }
83 }
84 }
85 }else{
86 echo $Translation["table uptodate"];
87 }
88 }else{
89 echo str_replace("<TableName>", $tableName, $Translation["couldnt count"]);
90 }
91 }else{ // given tableName doesn't exist
92
93 if($oldTableName!=''){ // if we have a table rename query
94 if($ro=@db_query("select count(1) from `$oldTableName`")){ // if old table exists, rename it.
95 $renameQuery=array_shift($arrAlter); // get and remove rename query
96
97 echo "$renameQuery ... ";
98 if(!@db_query($renameQuery)){
99 echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
100 echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
101 }else{
102 echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
103 }
104
105 if(is_array($arrAlter)) setupTable($tableName, $createSQL, false, $arrAlter); // execute Alter queries on renamed table ...
106 }else{ // if old tableName doesn't exist (nor the new one since we're here), then just create the table.
107 setupTable($tableName, $createSQL, false); // no Alter queries passed ...
108 }
109 }else{ // tableName doesn't exist and no rename, so just create the table
110 echo str_replace("<TableName>", $tableName, $Translation["creating table"]);
111 if(!@db_query($createSQL)){
112 echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
113 echo '<div class="text-danger">' . $Translation['mysql said'] . db_error(db_link()) . '</div>';
114 }else{
115 echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
116 }
117 }
118 }
119
120 echo "</div>";
121
122 $out=ob_get_contents();
123 ob_end_clean();
124 if(!$silent){
125 echo $out;
126 }
127 }
128 ?>
